package dao;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import model.HistoryModel;
import util.dataUtil;

public class HistoryDao {
	public static ArrayList getALL(){
		ArrayList listHistory = new ArrayList();
		
			try {
				String sql = "SELECT * FROM History";
				PreparedStatement ps = dataUtil.getconnConnection().prepareStatement(sql);
				ResultSet rs = ps.executeQuery();
				while (rs.next()){
					HistoryModel model = new HistoryModel();
					model.setId(rs.getInt("ID"));
					model.setName(rs.getString("name"));
					model.setDate(rs.getString("DATE"));
					model.setDetail(rs.getString("DETAIL"));
					model.setLastUpdated(rs.getString("Last_updated"));
					model.setPlace(rs.getString("PLACE"));
					listHistory.add(model);
				}
					
			} catch (Exception e) {
				e.printStackTrace();
			}
			return listHistory;
	}
	//Search history
	public static ArrayList getDataSearchHIS(String name){
		ArrayList listHistory = new ArrayList();
		
			try {
				String sql = "SELECT * FROM History where name like '%"+name+"%'";
				PreparedStatement ps = dataUtil.getconnConnection().prepareStatement(sql);
				ResultSet rs = ps.executeQuery();
				while (rs.next()){
					HistoryModel model = new HistoryModel();
					model.setId(rs.getInt("ID"));
					model.setName(rs.getString("name"));
					model.setDate(rs.getString("DATE"));
					model.setDetail(rs.getString("DETAIL"));
					model.setLastUpdated(rs.getString("Last_updated"));
					model.setPlace(rs.getString("PLACE"));
					listHistory.add(model);
				}
					
			} catch (Exception e) {
				e.printStackTrace();
			}
			return listHistory;
	}
	
	public static ArrayList getbyID(int ID){
		ArrayList listHistory = new ArrayList();
		HistoryModel model = null;
		String sql = "SELECT * FROM History where ID =?";
		try {
			PreparedStatement ps = dataUtil.getconnConnection()
					.prepareStatement(sql);
			ps.setInt(1, ID);
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				model = new HistoryModel();
				model.setId(rs.getInt("ID"));
				model.setName(rs.getString("name"));
				model.setDate(rs.getString("DATE"));
				model.setDetail(rs.getString("DETAIL"));
				model.setLastUpdated(rs.getString("Last_updated"));
				model.setPlace(rs.getString("PLACE"));
				listHistory.add(model);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return listHistory;
		
	}
	public static int updateHistory(HistoryModel model){
		int kq =0;
		try {
			CallableStatement csmt = dataUtil.getconnConnection().prepareCall("{call sp_UpdateHISTORY(?,?,?,?,?,?,?)}");
			csmt.setInt("id", model.getId());
			csmt.setString("name", model.getName());
			csmt.setString("DATE", model.getDate());
			csmt.setString("DETAIL", model.getDetail());
			csmt.setString("PLACE", model.getPlace());
			csmt.setString("Last_updated", model.getLastUpdated());
			csmt.registerOutParameter("result", java.sql.Types.INTEGER);
			csmt.execute();
			kq = csmt.getInt("result");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return kq;
	}
	public static int insertHistory(HistoryModel model){
		int kq =0;
		try {
			CallableStatement csmt = dataUtil.getconnConnection().prepareCall("{call sp_InsertHISTORY(?,?,?,?,?,?)}");
			csmt.setString("name", model.getName());
			csmt.setString("DATE", model.getDate());
			csmt.setString("DETAIL", model.getDetail());
			csmt.setString("PLACE", model.getPlace());
			csmt.setString("Last_updated", model.getLastUpdated());
			csmt.registerOutParameter("result", java.sql.Types.INTEGER);
			csmt.execute();
			kq = csmt.getInt("result");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return kq;
	}
	public static int deleteHistory(HistoryModel model){
		int kq =0;
		try {
			CallableStatement csmt = dataUtil.getconnConnection().prepareCall("{call sp_DeleteHISTORY(?,?)}");
			csmt.setInt("id", model.getId());
			csmt.registerOutParameter("result", java.sql.Types.INTEGER);
			csmt.execute();
			kq = csmt.getInt("result");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return kq;
	}
}
